﻿CREATE PROCEDURE [dbo].[proc_Customer_Getlist_In]
(
		@InCompanyId varchar(500),
		@SourceId int,
		@TypeId int,
		@StateId int,
		@ProtectId int,
		@IndustryOneId int,
		@IndustryTwoId int,
		@Stype nvarchar(50),
		@Stext nvarchar(50),
		@Pid int,
		@Cid int,
		@Rid int,
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
DECLARE @cSql nvarchar(4000),@cWhere nvarchar(1000)
If @StartDate='2006-01-01'
	Set @cWhere = 'CompanyId In('+@InCompanyId+')'
Else
	Set @cWhere = 'CompanyId In('+@InCompanyId+') And CreateDate>=@sRq And CreateDate<=@eRq'

If @SourceId>0
	Set @cWhere=@cWhere+' And SourceId='+Convert(varchar(10),@SourceId)
If @TypeId>0
	Set @cWhere=@cWhere+' And TypeId='+Convert(varchar(10),@TypeId)
If @StateId>0
	Set @cWhere=@cWhere+' And StateId='+Convert(varchar(10),@StateId)
If @ProtectId>0
	Set @cWhere=@cWhere+' And ProtectId='+Convert(varchar(10),@ProtectId)

IF @Pid<>0 --省
	SET @cWhere=@cWhere+' AND Pid='+Convert(varchar(10),@Pid)
IF @Cid<>0 --市
	SET @cWhere=@cWhere+' AND Cid='+Convert(varchar(10),@Cid)
IF @Rid<>0 --区
	SET @cWhere=@cWhere+' AND Rid='+Convert(varchar(10),@Rid)

If @IndustryOneId>0
Begin
	If @IndustryTwoId>0
		Set @cWhere=@cWhere+' And IndustryTwoId='+Convert(varchar(10),@IndustryTwoId)
	Else If @IndustryTwoId=-1
		Set @cWhere=@cWhere+' And IndustryOneId='+Convert(varchar(10),@IndustryOneId)+' And IndustryTwoId Not In(Select Id From Customer_Industry Where FId='+Convert(varchar(10),@IndustryOneId)+')'
	Else
		Set @cWhere=@cWhere+' And IndustryOneId='+Convert(varchar(10),@IndustryOneId)
End

If @IndustryOneId=-1
	Set @cWhere=@cWhere+' And IndustryOneId Not In (Select Id From Customer_Industry Where FId=0)'

If @Stext<>''
	SET @cWhere=@cWhere+' And '+@Stype+' Like ''%'+@Stext+'%'' '

	Set @cSql='Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,''' + @StartDate + ' 00:00:00'')
	Set @eRq = Convert(Datetime,''' + @EndDate + ' 23:59:59'')

	;WITH List As(Select ROW_NUMBER() OVER (Order By Id Desc) AS Row,
		Id,CustomerId,CustomerName,Project,Tel,
		IndustryOneId,IndustryTwoId,SourceId,TypeId,StateId,ProtectId,
		-- IsNull((Select Title From Customer_Industry Where FId=0 And Id=C.IndustryOneId),''...'') As IndustryOne,
		-- IsNull((Select Title From Customer_Industry Where FId=C.IndustryOneId And Id=C.IndustryTwoId),''...'') As IndustryTwo,
		-- IsNull((Select Title From Customer_Source Where Id=C.SourceId),''...'') As Source,
		-- IsNull((Select Title From Customer_Type Where Id=C.TypeId),''...'') As Type,
		-- IsNull((Select Title From Customer_State Where Id=C.StateId),''...'') As State,
		-- IsNull((Select Title From Customer_Protect Where Id=C.ProtectId),''...'') As Protect,
		Audit,delFlag
		
	From Customer C
	Where '+@cWhere+'
	)
	Select Id,CustomerId,CustomerName,Project,Audit,delFlag,
	IsNull((Select Title From Customer_Industry Where FId=0 And Id=List.IndustryOneId),''...'') As IndustryOne,
	IsNull((Select Title From Customer_Industry Where FId=List.IndustryOneId And Id=List.IndustryTwoId),''...'') As IndustryTwo,
	IsNull((Select Title From Customer_Source Where Id=List.SourceId),''...'') As Source,
	IsNull((Select Title From Customer_Type Where Id=List.TypeId),''...'') As Type,
	IsNull((Select Title From Customer_State Where Id=List.StateId),''...'') As State,
	IsNull((Select Title From Customer_Protect Where Id=List.ProtectId),''...'') As Protect,
	--Isnull((select Pizhu from Customer_PiZhu where CustomerId=List.CustomerId),'''') as Pizhu, 
	--Isnull((select CusState from Customer_PiZhu where CustomerId=List.CustomerId),0) as CusState,
	'
    
	If @StartDate='2006-01-01'
		Set @cSql=@cSql+'(Select COUNT(0) From Customer_Track WHERE CustomerId=List.CustomerId) As TrackCount,'
	Else
		Set @cSql=@cSql+'0 As TrackCount,'

	Set @cSql=@cSql+'
	(Select Count(0) From List) As RecordCount
	From List 
	Where Row Between '+Convert(varchar(10),@StartIndex)+' And '+Convert(varchar(10),@EndIndex) + '
	Order By Row'
   
	Exec(@cSql)
End
